<?php
/**
@file db.php
@brief 
	stores db related funtions
*/
/**
insert multiple database query statements from a piece of string

@param $sql_file
The sql file to be imported into database

@return 
returns false on fail
*/
function execSQLFile($sql_file){
	$lotsofqueries = '';
	//open file
	$file_handle = fopen($sql_file, "r");
	if(!$file_handle){
		return false;
	}
	while (!feof($file_handle)) {
	   $line = fgets($file_handle);
		if(!starts_with($line,'--') && !starts_with($line,'/*')){
			$lotsofqueries .=$line;
		}
	}
	fclose($file_handle);
	//parse query by ';'
	$query_array = explode(';',$lotsofqueries);
	foreach($query_array as $key => $value){
		if(trim($value) != ''){
			DB::query($value);
		}
	}
	return true;
}


/**
Function to delete a category

@param $cat_id
	 id of the category to be deleted

*/
function deleteCategory($cat_id){
	DB::query("DELETE FROM `category` WHERE `id`='$cat_id'");
	DB::query("DELETE FROM `item` WHERE `category_id`='$cat_id'");
}
/**
Function to delete a category

@param $item_id
	 id of the item to be deleted

*/
function deleteItem($item_id){
	DB::query("DELETE FROM `item` WHERE `id`='$item_id'");
}



/**
Get all categories from database

@param $orderby
	order by NAME(default) or TIME

@return 
	the query result as an assosiative array
*/

function getCategories($orderby='NAME'){
	if($orderby == 'TIME'){
		return DB::query("SELECT * FROM  `category` ORDER BY `id` ASC");
	}
	if($orderby == 'NAME'){
		return DB::query("SELECT * FROM  `category` ORDER BY `name` ASC");
	}
}

/**
Get all items from a category

@param $cat_id
	id of the category to select items from

@param $orderby
	order by NAME(default) or TIME

@return 
	the query result as an assosiative array
*/
function getItems($cat_id,$orderby='NAME'){
	if($orderby == 'TIME'){
		return DB::query("SELECT * FROM `item` WHERE `category_id`='".$cat_id."' ORDER BY `id` ASC");
	}
	if($orderby == 'NAME'){
		return DB::query("SELECT * FROM `item` WHERE `category_id`='".$cat_id."' ORDER BY `name` ASC");
	}
}


/**
Get all items from a category

@param $cat_id
	id of the category to select items from

@param $orderby
	order by NAME(default) or TIME

@return 
	the query result as an assosiative array
*/
function getItemById($item_id){
	return DB::queryFirstRow("SELECT * FROM `item` WHERE `id`='$item_id'");
}


/**
Function to be used as ajax function to get the list of items according to category id

@param $cat_id
	 id of the category to get items from

@return 
	the options to fill in items selection
*/
function getItemByCategory($cat_id, $orderby='NAME'){
	if($orderby == 'TIME'){
		$items = DB::query("SELECT * FROM `item` WHERE `category_id`='".$cat_id."' ORDER BY `id` ASC");
	}
	if($orderby == 'NAME'){
		$items = DB::query("SELECT * FROM `item` WHERE `category_id`='".$cat_id."' ORDER BY `name` ASC");
	}
    return json_encode($items); 
}

/**
Get category details by category id

@param $cat_id
	 id of the category to get details from

@return 
	category query result as associative array
*/
function getCategoryById($cat_id){
	return DB::queryFirstRow("SELECT * FROM `category` WHERE `id`='$cat_id'");
}


/**
Edit item
@param
	$item_id
		the item that need to be updated
	$name
		new name of item
	$note
		new note
	$cat_id
		id for new category
	$hours
		new hours per item
	$show_hour
		new show_hour condition
		 
*/
function updateItem($item_id, $name, $note, $cat_id, $hours, $show_hour){
	$update_array = array(
		'name' => $name,
		'note' => $note,
		'category_id' => $cat_id,
		'hours_per_item' => $hours,
		'show_hour' => $show_hour
	);
	 DB::update('item', $update_array, "id=%d", $item_id);
}



function updateCategory($cat_id,$name,$note,$rate){
	$update_array = array(
		'name' => $name,
		'note' => $note,
		'rate' => $rate
	);
	 DB::update('category', $update_array, "id=%d", $cat_id);
}



function getColumns($table){
	$fields = DB::queryFirstRow('SELECT * FROM `'.$table.'`');
	if(is_array($fields) && count($fields)>0){
		return array_keys($fields);//only need the titles of fields	
	}else{
		return false;
	}
}


/*
require_once(ROOT_DIR.'/lib/PHPLiveX.php');

$ajax = new PHPLiveX(array("getItemByCategory")); 
$ajax->Run();
*/




?>